package com.lei.springbootpractice.config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class NotificationTableCreator {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void createNotificationTable() {
        try {
            // 删除已存在的表（如果存在）
            jdbcTemplate.execute("DROP TABLE IF EXISTS notification_status");
            System.out.println("删除旧的notification_status表（如果存在）");

            // 创建通知状态表
            String createTableSql = """
                CREATE TABLE notification_status (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY,
                    user_id VARCHAR(50) NOT NULL,
                    user_role VARCHAR(20) NOT NULL,
                    notification_type VARCHAR(20) NOT NULL,
                    related_id VARCHAR(50),
                    message_content VARCHAR(500),
                    sender_name VARCHAR(100),
                    is_read BOOLEAN DEFAULT FALSE,
                    created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
                """;
            jdbcTemplate.execute(createTableSql);
            System.out.println("创建notification_status表成功");

            // 创建索引
            jdbcTemplate.execute("CREATE INDEX IX_notification_status_user_id ON notification_status(user_id)");
            jdbcTemplate.execute("CREATE INDEX IX_notification_status_is_read ON notification_status(is_read)");
            jdbcTemplate.execute("CREATE INDEX IX_notification_status_created_time ON notification_status(created_time)");
            System.out.println("创建索引成功");

            // 插入测试数据
            String insertSql = """
                INSERT INTO notification_status (user_id, user_role, notification_type, related_id, message_content, sender_name, is_read) 
                VALUES 
                ('customer1', 'customer', 'new_message', 'conv001', '您好，有什么可以帮助您的吗？', '客服小王', FALSE),
                ('service1', 'customer_service', 'new_message', 'conv001', '我想咨询一下产品信息', '张三', FALSE),
                ('customer2', 'customer', 'conversation_started', 'conv002', '发起了新的客服咨询', '李四', FALSE)
                """;
            jdbcTemplate.execute(insertSql);
            System.out.println("插入测试数据成功");

            // 验证表创建
            Integer count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM notification_status", Integer.class);
            System.out.println("notification_status表验证成功，当前有 " + count + " 条记录");

        } catch (Exception e) {
            System.err.println("创建notification_status表失败: " + e.getMessage());
            e.printStackTrace();
        }
    }
} 